package com.whu.exp1jsp.dao;

import com.whu.exp1jsp.pojo.UserSt;
import com.whu.exp1jsp.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {

    // 插入新用户
    public void insertUser(UserSt user) throws SQLException {
        String sql = "INSERT INTO user_st (user_id, user_name, user_account, user_password, user_banned, is_manager) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, user.getUserId());
            ps.setString(2, user.getUserName());
            ps.setString(3, user.getUserAccount());
            ps.setString(4, user.getUserPassword());
            ps.setInt(5, user.getUserBanned());
            ps.setInt(6, user.getIsManager());
            ps.executeUpdate();
        }
    }

    // 根据用户ID删除用户
    public void deleteUserById(long userId) throws SQLException {
        String sql = "DELETE FROM user_st WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, userId);
            ps.executeUpdate();
        }
    }

    // 更新用户信息
    public boolean updateUser(UserSt user) throws SQLException {
        String sql = "UPDATE user_st SET user_name = ?, user_account = ?, " +
                "user_password = ?, user_banned = ?, is_manager = ? " +
                "WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getUserAccount());
            ps.setString(3, user.getUserPassword());
            ps.setInt(4, user.getUserBanned());
            ps.setInt(5, user.getIsManager());
            ps.setLong(6, user.getUserId());
            int rowsUpdated = ps.executeUpdate();
            return rowsUpdated > 0;
        }
    }

    // 根据用户ID查询用户
    public UserSt selectUserByPhoneNum(String phoneNum) throws SQLException {
        String sql = "SELECT user_id, user_name, user_account, user_password, user_banned, is_manager " +
                "FROM user_st WHERE user_account = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, phoneNum);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new UserSt(
                            rs.getLong("user_id"),
                            rs.getString("user_name"),
                            rs.getString("user_account"),
                            rs.getString("user_password"),
                            rs.getInt("user_banned"),
                            rs.getInt("is_manager")
                    );
                } else {
                    return null;
                }
            }
        }
    }

    // 查询全部用户
    public List<UserSt> getAllUser() throws SQLException {
        String sql = "SELECT user_id, user_name, user_account, user_password, user_banned, is_manager FROM user_st";
        List<UserSt> userList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                userList.add(new UserSt(
                        rs.getLong("user_id"),
                        rs.getString("user_name"),
                        rs.getString("user_account"),
                        rs.getString("user_password"),
                        rs.getInt("user_banned"),
                        rs.getInt("is_manager")
                ));
            }
        }
        return userList;
    }
}
